import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class jdbc_secret_equivalence_query_0001 {
   //创建数据库连接。
   public static Connection GetConnection(String username, String passwd) {
        String driver = "org.postgresql.Driver";
        String sourceURL = "jdbc:postgresql://host_ip:db_port/database_name?url_params";
        Connection conn = null;
        try {
            //创建数据库连接。
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    return conn;
    }
    //执行SQL语句，创建加密表
    public static void CreateTable(Connection conn) {
        try {
            //创建CMK
            Statement stmt = conn.createStatement();
            int rc1 = stmt.executeUpdate("drop client master key if exists imgcmk1;");
            int rc2 = stmt.executeUpdate("create client master key imgcmk1 with (key_store = localkms, key_path = 'key_path_value' , algorithm = rsa_3072);");
            //创建CEM
            int rc3 = stmt.executeUpdate("drop column encryption key if exists imgcek1;");
            int rc4 = stmt.executeUpdate("create column encryption key imgcek1 with values (client_master_key = imgcmk1, algorithm  = aead_aes_256_cbc_hmac_sha256);");
            //创建加密表
            int rc5 = stmt.executeUpdate("drop table if exists t_qeuivalence_case001;");
            int rc6 = stmt.executeUpdate("create table t_qeuivalence_case001 (id_number int, name varchar(50) encrypted with (column_encryption_key = imgcek1, encryption_type = deterministic),credit_card  varchar(19) encrypted with (column_encryption_key = imgcek1, encryption_type = deterministic));");
            // 插入数据
            int rc7 = stmt.executeUpdate("insert into t_qeuivalence_case001 values (1,'joe','6217986500001288393');");
            // 查询加密表
            ResultSet rs = null;
            rs = stmt.executeQuery("select credit_card from t_qeuivalence_case001 where name = 'joe';");
            stmt.close();
            System.out.println("select_result = " + rs);
            System.out.println("加密表查询成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Faild:select_result = " + e.getMessage());
            System.out.println("加密表查询失败！");
        }
        try {
            // 调用Connection的prepareStatement方法创建预编译语句对象
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO t_qeuivalence_case001 VALUES (?, ?, ?);");
            // 调用PreparedStatement的setShort设置参数。
            pstmt.setInt(1, 2);
            pstmt.setString(2, "joy");
            pstmt.setString(3, "6219985678349800033");
            // 调用PreparedStatement的executeUpdate方法执行预编译SQL语句
            int rowcount = pstmt.executeUpdate();
            pstmt.close();
            System.out.println("insert_result = " + rowcount);
            System.out.println("预编译SQL语句执行成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("insert_result = " + e.getMessage());
            System.out.println("预编译SQL语句执行失败！");
        }
        try {
            //清理环境
            Statement stmt_drop = conn.createStatement();
            int clear_rc1 = stmt_drop.executeUpdate("drop table t_qeuivalence_case001 cascade;");
            int clear_rc2 = stmt_drop.executeUpdate("drop column encryption key imgcek1 cascade;");
            int clear_rc3 = stmt_drop.executeUpdate("drop client master key if exists imgcmk1 cascade;");
            stmt_drop.close();
            System.out.println("drop_result = " + clear_rc3);
            System.out.println("Execution Succeeded!");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("drop_result = " + e.getMessage());
            System.out.println("Execution Faild!");
        }
    }
    public static void main(String[] args) throws InterruptedException {
        //创建数据库连接。
        Connection conn = GetConnection("conn_user", "conn_passwd");
        //创建表。
        CreateTable(conn);
        //关闭数据库连接。
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
   }
}